package com.lovo.roomProject.room.dao.impl;

import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.*;
import com.lovo.roomProject.room.dao.RoomDAO;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class RoomDAOImpl extends BaseDAO implements RoomDAO {
    @Override
    public List listRoomByType(String type) {
        this.getConnection();
        String sql = "SELECT\n" +
                "\tr.r_id,\n" +
                "\tr.r_addr,\n" +
                "\tr.r_count,\n" +
                "\tCOUNT( s.s_id ) \n" +
                "FROM\n" +
                "\tt_room r\n" +
                "\tLEFT JOIN t_student s ON r.r_id = s.s_r_id \n" +
                "WHERE\n" +
                "\tr.r_type = ? \n" +
                "GROUP BY\n" +
                "\tr.r_id,\n" +
                "\tr.r_addr,\n" +
                "\tr.r_count \n" +
                "HAVING\n" +
                "\tCOUNT( s.s_id ) < r.r_count\n";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, type);
            rs = statement.executeQuery();
            while (rs.next()) {
                RoomDO roomDO = new RoomDO();
                roomDO.setId(rs.getInt("r_id"));
                roomDO.setAddr(rs.getString("r_addr"));
                roomDO.setCount(rs.getInt("r_count"));
                list.add(roomDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return list;
    }

    @Override
    public List listRoom() {
        this.getConnection();
        String sql = "SELECT r.*,COUNT(s.s_id) cnt FROM t_room r LEFT JOIN t_student s on r.r_id = s.s_r_id GROUP BY r.r_id";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();
            while (rs.next()) {
                RoomDO roomDO = new RoomDO();
                roomDO.setId(rs.getInt("r_id"));
                roomDO.setAddr(rs.getString("r_addr"));
                roomDO.setCount(rs.getInt("r_count"));
                roomDO.setInCount(rs.getInt("cnt"));
                roomDO.setStatus(rs.getString("r_status"));
                roomDO.setType(rs.getString("r_type"));
                list.add(roomDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return list;
    }


    @Override
    public void insert(RoomDO roomDO) {
        this.getConnection();
        String sql = "INSERT INTO t_room(r_addr,r_count,r_status,r_type) values (?,?,?,?)";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, roomDO.getAddr());
            statement.setObject(2, roomDO.getCount());
            statement.setObject(3, "正常");
            statement.setObject(4, roomDO.getType());
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement);
        }
    }

    @Override
    public void delete(int id) {
        this.getConnection();
        String sql = "DELETE FROM t_room WHERE r_id = ?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement);
        }
    }

    @Override
    public RoomDO getById(int id) {
        this.getConnection();
        String sql = "SELECT *,COUNT(s.s_id) cnt FROM t_room r LEFT JOIN t_student s ON r.r_id = s.s_r_id WHERE r.r_id = ? GROUP BY r.r_id";
        RoomDO roomDO = new RoomDO();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();
            if (rs.next()) {
                roomDO.setId(rs.getInt("r_id"));
                roomDO.setAddr(rs.getString("r_addr"));
                roomDO.setCount(rs.getInt("r_count"));
                roomDO.setInCount(rs.getInt("cnt"));
                roomDO.setType(rs.getString("r_type"));
                roomDO.setStatus(rs.getString("r_status"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return roomDO;
    }

    @Override
    public List getStudent(int id) {
        this.getConnection();
        String sql = "SELECT  r.r_id,c.c_name,s.s_id,s.s_name,s.s_phone FROM t_student s join t_class c on s.s_c_id=c.c_id JOIN t_room r on s.s_r_id= r.r_id WHERE r_id= ?";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();
            while (rs.next()) {
                StudentDO studentDO = new StudentDO();
                ClaszDO claszDO = new ClaszDO();
                claszDO.setName(rs.getString("c_name"));
                studentDO.setId(rs.getInt("s_id"));
                studentDO.setName(rs.getString("s_name"));
                studentDO.setPhone(rs.getString("s_phone"));
                studentDO.setClasz(claszDO);
                list.add(studentDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return list;
    }

    @Override
    public CutPageBean listRoomByPage(int current, String addr, String status, String enableRoom, String roomType) {
        this.getConnection();
        String selectSql = " SELECT r.r_id,r.r_addr,r.r_status,r.r_count,r.r_type,COUNT(s.s_id) stucnt  ";
        String countSql = " select count(*) cnt ";
        String fromSql = " FROM t_room r LEFT JOIN t_student s ON r.r_id = s.s_r_id ";
        String bySql = " GROUP BY r.r_id ";
        String limitSql = " limit ?,? ";
        //动态组装
        String dynamicSql = "WHERE 1=1 ";
        if (addr != null && !addr.equals("")) {
            dynamicSql += " and r.r_addr like '%" + addr + "%' ";
        }
        if (!status.equals("不限")) {
            dynamicSql += " and r.r_status = '" + status + "' ";
        }
        if (!roomType.equals("不限")) {
            dynamicSql += " and r.r_type ='" + roomType + "' ";
        }
        // 可住房间
        if (!enableRoom.equals("不限")) {
            bySql += " having count(s.s_id)<r.r_count ";
        }
        System.out.println(selectSql + fromSql + dynamicSql + bySql + limitSql);
        CutPageBean cutPageBean = new CutPageBean();
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(selectSql + fromSql + dynamicSql + bySql + limitSql);
            statement.setObject(1, (current - 1) * CutPageBean.PAGESIZE);
            statement.setObject(2, CutPageBean.PAGESIZE);
            System.out.println(selectSql + fromSql + dynamicSql + bySql + limitSql + "  2");
            rs = statement.executeQuery();
            while (rs.next()) {
                RoomDO roomDO = new RoomDO();
                roomDO.setId(rs.getInt("r.r_id"));
                roomDO.setAddr(rs.getString("r.r_addr"));
                roomDO.setCount(rs.getInt("r.r_count"));
                roomDO.setInCount(rs.getInt("stucnt"));
                roomDO.setType(rs.getString("r.r_type"));
                roomDO.setStatus(rs.getString("r.r_status"));
                list.add(roomDO);
            }
            //计算总数的sql
            cutPageBean.setList(list);
            statement = con.prepareStatement(countSql + fromSql + dynamicSql);
            rs = statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return cutPageBean;
    }

    @Override
    public List listFacility(int roomId) {
        this.getConnection();
        String sql = " SELECT * FROM t_room r RIGHT JOIN t_facility f ON r.r_id = f.f_r_id WHERE r.r_id =  ?";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, roomId);
            rs = statement.executeQuery();
            while (rs.next()) {
                FacilityDO facilityDO = new FacilityDO();
                facilityDO.setId(rs.getInt("f.f_id"));
                facilityDO.setDescription(rs.getString("f.f_description"));
                facilityDO.setErrorDate(LocalDate.parse(rs.getString("f.f_error_date")));
                facilityDO.setSolve(rs.getString("f.f_solve"));
                list.add(facilityDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return list;
    }

    @Override
    public void insertFacility(FacilityDO facilityDO, int roomId) {
        this.getConnection();
        String sql = "INSERT INTO t_facility(f_description,f_error_date,f_solve,f_r_id) values (?,?,?,?)";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, facilityDO.getDescription());
            statement.setObject(2, LocalDate.now());
            statement.setObject(3, facilityDO.getSolve());
            statement.setObject(4, roomId);
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement);
        }
    }

    @Override
    public void updateFacility(int roomId) {
        this.getConnection();
        String sql = "UPDATE t_facility set f_solve = '是'  WHERE f_id = ?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, roomId);
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement);
        }
    }

    @Override
    public List updateRoomStatus(int roomId) {
        List list = listFacility(roomId);
        String str = "正常";
        for (Object facility : list) {
            if (((FacilityDO) facility).getSolve().equals("否")) {
                str = "损坏";
            }
        }
        this.getConnection();
        String sql = "UPDATE t_room set r_status = ?  WHERE r_id = ?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, str);
            statement.setObject(2, roomId);
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement);
        }
        return list;
    }
}
